﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;

using System.Data.SqlClient;
using prjKQXS_DLL;

namespace prjKQXS_UI.View
{
    public partial class frmUpdateDL : DevExpress.XtraEditors.XtraForm
    {
        public frmUpdateDL()
        {
            InitializeComponent();
        }

        private void labelControl1_MouseHover(object sender, EventArgs e)
        {
            this.labelControl1.Appearance.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Underline);
        }

        private void labelControl1_MouseLeave(object sender, EventArgs e)
        {
            this.labelControl1.Appearance.Font = new System.Drawing.Font("Tahoma", 8.25F);
        }

        private void labelControl1_Click(object sender, EventArgs e)
        {
            if (!isRunning)
            {
                this.Close();
            }
            else
            {
                clsCommon.MsgBox("Đang tiến hành chuyển Dữ liệu, không thể thoát", 0);
            }
        }

        private void frmUpdateDL_Load(object sender, EventArgs e)
        {
            Control.CheckForIllegalCrossThreadCalls = false;
            backgroundWorker1.RunWorkerAsync();
        }
        private void closeConn(SqlConnection conn)
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        private void openConn(SqlConnection conn)
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
        }
        public bool kiemtra(string sql)
        {
            SqlCommand sqlcom;
         
            SqlConnection connC = new SqlConnection(BienTc.connString);
            sqlcom = new SqlCommand(sql, connC);
            int n = (int)sqlcom.ExecuteScalar();
            connC.Close();
            if (n > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {
            labelControl2.Text = "Đang kiểm tra kết nối đến máy chủ .... ";
            SqlConnection connS = new SqlConnection(clsCommon.getServerConnectionString());
            SqlConnection connN = new SqlConnection(BienTc.connString);

            try
            {
                if (BienTc.isThanhPho || BienTc.isQuanHuyen)
                {
                    #region
                    SqlCommand cmd = new SqlCommand("Select * from DONVI", connS);
                    openConn(connS);
                    SqlDataReader dr = cmd.ExecuteReader();
                    if (dr.Read())
                    {
                        dr.Close();
                        labelControl2.Text = "\r\nKết Nối Thành công !";
                        labelControl2.Text = "\r\nBắt đầu chuyển dữ liệu hợp đồng ... ";
                        SqlConnection connC = new SqlConnection(BienTc.connString); connC.Open();
                        if (connS.State == ConnectionState.Closed) connS.Open();

                        // chuyen can bo
                        SqlCommand ccc = new SqlCommand("delete from DMCANBO where Ma_DT is null or len(Ma_DT) =0", connS);
                        ccc.ExecuteNonQuery();
                        DataTable dtcanbo = clsCommon.FillData("Select * from DMCanBo", connS);
                     
                        foreach (DataRow row in dtcanbo.Rows)
                        {
                            SqlDataAdapter dac = new SqlDataAdapter("Select * from DMCanBo Where Ma_dt=@madt", connC);
                            dac.SelectCommand.Parameters.AddWithValue("@madt", row["Ma_DT"]);
                            DataTable dtc = new DataTable();
                            dac.Fill(dtc);

                            if (dtc.Rows.Count <= 0)
                            {
                                SqlCommand cmdcb = new SqlCommand("insert into dbo.DMCANBO(	Ma_DT,	Ten_DT,	SDT,	Ma_DMChucVu,	Tuyen,	Ma_DMDonVi,	Noi_O,	MST,	CMT,	Noi_Cap,	Ngay_Cap,	isPublic,	isBanRong) values(	@Ma_DT,	@Ten_DT,	@SDT,	@Ma_DMChucVu,	@Tuyen,	@Ma_DMDonVi,	@Noi_O,	@MST,	@CMT,	@Noi_Cap,	@Ngay_Cap,	@isPublic,	@isBanRong)", connC);
                                cmdcb.Parameters.AddWithValue("@Ma_DT", row["Ma_DT"]);
                                cmdcb.Parameters.AddWithValue("@Ten_DT", row["Ten_DT"]);
                                cmdcb.Parameters.AddWithValue("@SDT", row["SDT"]);
                                cmdcb.Parameters.AddWithValue("@Ma_DMChucVu", row["Ma_DMChucVu"]);
                                cmdcb.Parameters.AddWithValue("@Tuyen", row["Tuyen"]);
                                cmdcb.Parameters.AddWithValue("@Ma_DMDonVi", row["Ma_DMDonVi"]);
                                cmdcb.Parameters.AddWithValue("@Noi_O", row["Noi_O"]);
                                cmdcb.Parameters.AddWithValue("@MST", row["MST"]);
                                cmdcb.Parameters.AddWithValue("@CMT", row["CMT"]);
                                cmdcb.Parameters.AddWithValue("@Noi_Cap", row["Noi_Cap"]);
                                cmdcb.Parameters.AddWithValue("@Ngay_Cap", row["Ngay_Cap"]);

                                cmdcb.Parameters.AddWithValue("@isPublic", true);
                                cmdcb.Parameters.AddWithValue("@isBanRong", row["isBanRong"]);
                                cmdcb.ExecuteNonQuery();
                               }
                           
                        }


                        //Chuyen Nhat ky chuyen dai ly
                        DataTable dtNK = clsCommon.FillData("Select * from NhatKyChuyenDL", connS);
                        foreach (DataRow row in dtNK.Rows)
                        {
                            SqlDataAdapter dankC = new SqlDataAdapter("Select * from NhatKyChuyenDL where ID_Update=@idupdate", connC);
                            dankC.SelectCommand.Parameters.AddWithValue("@idupdate", row["ID_Update"]);
                            DataTable dtnkc = new DataTable();
                            dankC.Fill(dtnkc);
                            if (dtnkc.Rows.Count <= 0)
                            { 
                                // insert nhat ky chuyen dai ly
                                SqlCommand cmdNK = new SqlCommand(@"Insert into NhatKyChuyenDL(Ngay_Chuyen,Ma_DLCu,Ma_DLMoi,Ten_DL,Ma_CBCu,Ma_CBMoi,ID_Update,Ngay_HD) values(
                                                                    @Ngay_Chuyen,@Ma_DLCu,@Ma_DLMoi,@Ten_DL,@Ma_CBCu,@Ma_CBMoi,@ID_Update,@Ngay_HD)",connC);
                                cmdNK.Parameters.AddWithValue("@Ngay_Chuyen", row["Ngay_Chuyen"]);
                                cmdNK.Parameters.AddWithValue("@Ma_DLCu", row["Ma_DLCu"]);
                                cmdNK.Parameters.AddWithValue("@Ma_DLMoi", row["Ma_DLMoi"]);
                                cmdNK.Parameters.AddWithValue("@Ten_DL", row["Ten_DL"]);
                                cmdNK.Parameters.AddWithValue("@Ma_CBCu", row["Ma_CBCu"]);
                                cmdNK.Parameters.AddWithValue("@Ma_CBMoi", row["Ma_CBMoi"]);
                                cmdNK.Parameters.AddWithValue("@ID_Update", row["ID_Update"]);
                                cmdNK.Parameters.AddWithValue("@Ngay_HD", row["Ngay_HD"]);
                                cmdNK.ExecuteNonQuery();
                            }
                        }

                        // chuyen hop dong                                            
                        DataTable dtt = clsCommon.FillData("Select * from HopDong ", connS);
                        foreach (DataRow row in dtt.Rows)
                        {
                            SqlDataAdapter dac = new SqlDataAdapter("Select * from HopDong Where Ma_DaiLy=@madaily", connC);                            
                            dac.SelectCommand.Parameters.AddWithValue("@madaily", row["Ma_DaiLy"]);
                            DataTable dtc = new DataTable();
                            dac.Fill(dtc);
                            if (dtc.Rows.Count <= 0)
                            {
                                SqlCommand cmdd = new SqlCommand("insert into dbo.[HOPDONG](Ma_DT,	Ngay_HD,	Ma_DMCanBo,	Ten_CanBo,	Noi_O,	MST,	CMT,	Noi_Cap,	Ngay_Cap,	Noi_Ban,	Ngay_Ban,	So_Tien,	Ma_DMTinhTrang,	Ma_DMLoaiVe,	Doanh_Thu,	Thanh_Ly,	Ngay_ThanhLy,	Ly_Do,	Ma_DMLoaiHD,	isPublic,	Ma_DaiLy,	Ten_DaiLy) values(	@Ma_DT,	@Ngay_HD,	@Ma_DMCanBo,	@Ten_CanBo,	@Noi_O,	@MST,	@CMT,	@Noi_Cap,	@Ngay_Cap,	@Noi_Ban,	@Ngay_Ban,	@So_Tien,	@Ma_DMTinhTrang,	@Ma_DMLoaiVe,	@Doanh_Thu,	@Thanh_Ly,	@Ngay_ThanhLy,	@Ly_Do,	@Ma_DMLoaiHD,	@isPublic,	@Ma_DaiLy,	@Ten_DaiLy)", connC);
                                cmdd.Parameters.AddWithValue("@Ma_DT", row["Ma_DT"]);
                                cmdd.Parameters.AddWithValue("@Ngay_HD", row["Ngay_HD"]);
                                cmdd.Parameters.AddWithValue("@Ma_DMCanBo", row["Ma_DMCanBo"]);
                                cmdd.Parameters.AddWithValue("@Ten_CanBo", row["Ten_CanBo"]);
                                cmdd.Parameters.AddWithValue("@Noi_O", row["Noi_O"]);
                                cmdd.Parameters.AddWithValue("@MST", row["MST"]);
                                cmdd.Parameters.AddWithValue("@CMT", row["CMT"]);
                                cmdd.Parameters.AddWithValue("@Noi_Cap", row["Noi_Cap"]);
                                cmdd.Parameters.AddWithValue("@Ngay_Cap", row["Ngay_Cap"]);
                                cmdd.Parameters.AddWithValue("@Noi_Ban", row["Noi_Ban"]);
                                cmdd.Parameters.AddWithValue("@Ngay_Ban", row["Ngay_Ban"]);
                                cmdd.Parameters.AddWithValue("@So_Tien", row["So_Tien"]);
                                cmdd.Parameters.AddWithValue("@Ma_DMTinhTrang", row["Ma_DMTinhTrang"]);
                                cmdd.Parameters.AddWithValue("@Ma_DMLoaiVe", row["Ma_DMLoaiVe"]);
                                cmdd.Parameters.AddWithValue("@Doanh_Thu", row["Doanh_Thu"]);
                                cmdd.Parameters.AddWithValue("@Thanh_Ly", row["Thanh_Ly"]);
                                cmdd.Parameters.AddWithValue("@Ngay_ThanhLy", row["Ngay_ThanhLy"]);
                                cmdd.Parameters.AddWithValue("@Ly_Do", row["Ly_Do"]);
                                cmdd.Parameters.AddWithValue("@Ma_DMLoaiHD", row["Ma_DMLoaiHD"]);
                                cmdd.Parameters.AddWithValue("@isPublic", row["isPublic"]);
                                cmdd.Parameters.AddWithValue("@Ma_DaiLy", row["Ma_DaiLy"]);
                                cmdd.Parameters.AddWithValue("@Ten_DaiLy", row["Ten_DaiLy"]);
                              cmdd.ExecuteNonQuery();


                            }
                            else
                            {
                                SqlCommand cmdd1 = new SqlCommand("update  dbo.[HOPDONG] set 	Ngay_HD=@Ngay_HD,	Ma_DMCanBo=@Ma_DMCanBo,	Ten_CanBo=@Ten_CanBo,Noi_O=@Noi_O,	MST=@MST,	CMT=@CMT,	Noi_Cap=@Noi_Cap,	Ngay_Cap=@Ngay_Cap,	Noi_Ban=@Noi_Ban,	Ngay_Ban=@Ngay_Ban,	So_Tien=@So_Tien,	Ma_DMTinhTrang=@Ma_DMTinhTrang,	Ma_DMLoaiVe=@Ma_DMLoaiVe,	Doanh_Thu=@Doanh_Thu,	Thanh_Ly=@Thanh_Ly,	Ngay_ThanhLy=@Ngay_ThanhLy,	Ly_Do=@Ly_Do,	Ma_DMLoaiHD=@Ma_DMLoaiHD,	isPublic=@isPublic,		Ten_DaiLy=@Ten_DaiLy where Ma_DT=@Ma_DT and Ma_DaiLy=@Ma_DaiLy", connC);
                                cmdd1.Parameters.AddWithValue("@Ma_DT", row["Ma_DT"]);
                                cmdd1.Parameters.AddWithValue("@Ngay_HD", row["Ngay_HD"]);
                                cmdd1.Parameters.AddWithValue("@Ma_DMCanBo", row["Ma_DMCanBo"]);
                                cmdd1.Parameters.AddWithValue("@Ten_CanBo", row["Ten_CanBo"]);
                                cmdd1.Parameters.AddWithValue("@Noi_O", row["Noi_O"]);
                                cmdd1.Parameters.AddWithValue("@MST", row["MST"]);
                                cmdd1.Parameters.AddWithValue("@CMT", row["CMT"]);
                                cmdd1.Parameters.AddWithValue("@Noi_Cap", row["Noi_Cap"]);
                                cmdd1.Parameters.AddWithValue("@Ngay_Cap", row["Ngay_Cap"]);
                                cmdd1.Parameters.AddWithValue("@Noi_Ban", row["Noi_Ban"]);
                                cmdd1.Parameters.AddWithValue("@Ngay_Ban", row["Ngay_Ban"]);
                                cmdd1.Parameters.AddWithValue("@So_Tien", row["So_Tien"]);
                                cmdd1.Parameters.AddWithValue("@Ma_DMTinhTrang", row["Ma_DMTinhTrang"]);
                                cmdd1.Parameters.AddWithValue("@Ma_DMLoaiVe", row["Ma_DMLoaiVe"]);
                                cmdd1.Parameters.AddWithValue("@Doanh_Thu", row["Doanh_Thu"]);
                                cmdd1.Parameters.AddWithValue("@Thanh_Ly", row["Thanh_Ly"]);
                                cmdd1.Parameters.AddWithValue("@Ngay_ThanhLy", row["Ngay_ThanhLy"]);
                                cmdd1.Parameters.AddWithValue("@Ly_Do", row["Ly_Do"]);
                                cmdd1.Parameters.AddWithValue("@Ma_DMLoaiHD", row["Ma_DMLoaiHD"]);
                                cmdd1.Parameters.AddWithValue("@isPublic", row["isPublic"]);
                                cmdd1.Parameters.AddWithValue("@Ma_DaiLy", row["Ma_DaiLy"]);
                                cmdd1.Parameters.AddWithValue("@Ten_DaiLy", row["Ten_DaiLy"]);

                                cmdd1.ExecuteNonQuery();
                            }

                        }

                        DataTable dt = clsCommon.FillData("select * from tblChuyenDL where isPublic = 1", connS);
                        foreach (DataRow row in dt.Rows)
                        {
                            cmd = new SqlCommand("Select count(*) from Hopdong Where isPublic=1 and Ma_DT=@madt", connN);
                            cmd.Parameters.AddWithValue("@madt", row["Ma_HD"]);
                            openConn(connN);
                            if (cmd.ExecuteScalar().ToString() != "0")
                            {
                                cmd = new SqlCommand("Update Hopdong set Ma_DaiLy=@madaily , Ma_DMCanBo=@macanbo where Ma_DT=" + row["Ma_HD"].ToString(), connN);
                                cmd.Parameters.AddWithValue("@madaily", row["Ma_DaiLyMoi"]);
                                cmd.Parameters.AddWithValue("@macanbo", row["Ma_CanBoMoi"]);
                                cmd.ExecuteNonQuery();

                                SqlDataAdapter da = new SqlDataAdapter("Select * from DoanhThu Where Ma_HopDong=@mahopdong", connN);
                                da.SelectCommand.Parameters.AddWithValue("@mahopdong", row["Ma_HD"]);
                                DataTable dt2 = new DataTable();
                                da.Fill(dt2);
                                if (dt2.Rows.Count > 0)
                                {
                                    cmd = new SqlCommand("Update DoanhThu set Ma_DaiLy=@madaily , Ma_DMCanBo=@macanbo Where  Ma_HopDong=@mahopdong", connN);
                                    cmd.Parameters.AddWithValue("@madaily", row["Ma_DaiLyMoi"]);
                                    cmd.Parameters.AddWithValue("@macanbo", row["Ma_CanBoMoi"]);
                                    cmd.Parameters.AddWithValue("@mahopdong", row["Ma_HD"]);
                                    cmd.ExecuteNonQuery();
                                }
                            }
                           
                              
                        }

                        clsCommon.MsgBox("Chuyển thành công", 1);
                        this.Close();
                    }
                    else
                    {
                        labelControl2.Text = "Không thể kết nối tới máy chủ!";
                    }
                    #endregion
                }
                else
                {
                    #region
                    SqlCommand cmd = new SqlCommand("Select * from DONVI", connS);
                    openConn(connS);
                    SqlDataReader dr = cmd.ExecuteReader();
                    if (dr.Read())
                    {
                        dr.Close();
                        labelControl2.Text = "\r\nKết Nối Thành công !";
                        labelControl2.Text = "\r\nBắt đầu chuyển dữ liệu hợp đồng ... ";

                        DataTable dt = clsCommon.FillData("select * from tblChuyenDL where isPublic = 1", connS);
                        foreach (DataRow row in dt.Rows)
                        {
                            cmd = new SqlCommand("Select count(*) from Hopdong Where isPublic=1 and Ma_DT=@madt", connS);
                            cmd.Parameters.AddWithValue("madt", row["Ma_HD"]);
                            openConn(connN);
                            if (cmd.ExecuteScalar().ToString() != "0")
                            {
                                cmd = new SqlCommand("Update Hopdong set Ma_DaiLy=@madaily , Ma_DMCanBo=@macanbo where Ma_DT=" + row["Ma_HD"].ToString(), connS);
                                cmd.Parameters.AddWithValue("@madaily", row["Ma_DaiLyMoi"]);
                                cmd.Parameters.AddWithValue("@macanbo", row["Ma_CanBoMoi"]);
                                cmd.ExecuteNonQuery();

                                SqlDataAdapter da = new SqlDataAdapter("Select * from DoanhThu Where Ma_HopDong=@mahopdong", connS);
                                da.SelectCommand.Parameters.AddWithValue("@mahopdong", row["Ma_HD"]);
                                DataTable dt2 = new DataTable();
                                da.Fill(dt2);
                                if (dt2.Rows.Count > 0)
                                {
                                    cmd = new SqlCommand("Update DoanhThu set Ma_DaiLy=@madaily , Ma_DMCanBo=@macanbo Where  Ma_HopDong=@mahopdong", connS);
                                    cmd.Parameters.AddWithValue("@madaily", row["Ma_DaiLyMoi"]);
                                    cmd.Parameters.AddWithValue("@macanbo", row["Ma_CanBoMoi"]);
                                    cmd.Parameters.AddWithValue("@mahopdong", row["Ma_HD"]);
                                    cmd.ExecuteNonQuery();
                                }
                            }
                        }

                        clsCommon.MsgBox("Chuyển thành công", 1);
                        this.Close();
                    }
                    else
                    {
                        labelControl2.Text = "Không thể kết nối tới máy chủ!";
                    }
                    #endregion
                }
            }
            catch (Exception)
            {
                clsCommon.MsgBox("Không thể kết nối tới máy chủ", 0);
            }
            finally
            {
                closeConn(connN);
                closeConn(connS);
            }
            // this.Close();
        }
        private bool isRunning = true;
        private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            isRunning = false;
        }

        private void labelControl3_Click(object sender, EventArgs e)
        {
            if (clsCommon.MsgBox("Dừng lại ?", 2) == System.Windows.Forms.DialogResult.Yes)
            {
                backgroundWorker1.CancelAsync();
                marqueeProgressBarControl1.Properties.Stopped = true;
                isRunning = false;
            }
        }

        private void marqueeProgressBarControl1_EditValueChanged(object sender, EventArgs e)
        {

        }
    }
}